﻿using MusicExplorer.Model;
using MusicExplorer.Repository;
using SQLite;
using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace MusicExplorer.Data
{
    public static class ServiceDatabase
    {
        private static string m_dbPath =
            System.IO.Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "data.db3");

        public static void InitializeDatabase()
        {
            using(var db = new SQLite.SQLiteConnection(m_dbPath))
            {
                // Création tables
                //db.DropTable<MusicExplorer.Data.Model.Category>();
                //db.DropTable<MusicExplorer.Data.Model.QueryGroup>();
                //db.DropTable<MusicExplorer.Data.Model.Query>();
                //db.DropTable<MusicExplorer.Data.Model.Entity>();
                //db.DropTable<MusicExplorer.Data.Model.Track>();
                //db.CreateTable<MusicExplorer.Data.Model.Category>();
                //db.CreateTable<MusicExplorer.Data.Model.QueryGroup>();
                //db.CreateTable<MusicExplorer.Data.Model.Query>();
                //db.CreateTable<MusicExplorer.Data.Model.Entity>();
                //db.CreateTable<MusicExplorer.Data.Model.Track>();
            }
        }

        public static async Task<Dictionary<Tuple<Guid, string>, bool>> AreTracksRead(IEnumerable<Track> tracks)
        {
            if (tracks == null || (tracks != null && !tracks.Any())) return new Dictionary<Tuple<Guid,string>,bool>();

            var db = new SQLiteAsyncConnection(m_dbPath);

            var builder = new StringBuilder();
            builder.Append("SELECT * FROM Tracks WHERE ");

            var i = 0;
            foreach(var track in tracks)
            {
                if(i > 0)
                {
                    builder.Append(" OR ");
                }
                builder.Append(string.Format("(ModelId = '{0}' AND RepositoryId = '{1}')",
                    track.Id, track.Repository.Id));
                i++;
            }

            var request = await db.QueryAsync<Model.Track>(builder.ToString());

            if(request.Count == 0)
            {
                return tracks.ToDictionary(t => new Tuple<Guid, string>(t.Repository.Id, t.Id), t => false);
            }

            var areTracksRead = new Dictionary<Tuple<Guid, string>, bool>();
            foreach(var track in tracks)
            {
                if(request.Exists(r => r.ModelId == track.Id && r.RepositoryId == track.Repository.Id && r.IsRead))
                {
                    areTracksRead.Add(new Tuple<Guid, string>(track.Repository.Id, track.Id), true);
                }
                else
                {
                    areTracksRead.Add(new Tuple<Guid, string>(track.Repository.Id, track.Id), false);
                }
            }

            return areTracksRead;
        }

        public static async Task InsertOrUpdateTrackAsync(Track track)
        {
            var db = new SQLiteAsyncConnection(m_dbPath);

            var request = await db.QueryAsync<Model.Track>(string.Format("SELECT * FROM Tracks WHERE ModelId = '{0}' AND RepositoryId = '{1}'",
                track.Id, track.Repository.Id));

            if (request.Count == 0)
            {
                var trackDb = Model.Track.New(track);
                await db.InsertAsync(trackDb);
            }
            else
            {
                await db.ExecuteAsync(string.Format("UPDATE Tracks SET IsRead = {0} WHERE ModelId = '{1}' AND RepositoryId = '{2}'",
                    track.IsRead ? 1 : 0, track.Id, track.Repository.Id));
            }
        }

        public static async Task<List<Category>> GetAllCategoriesAsync()
        {
            var db = new SQLiteAsyncConnection(m_dbPath);

            var categories = new List<Category>();
            var categoriesDb = await db.QueryAsync<Model.Category>("SELECT * FROM Categories");
            if(categoriesDb != null && categoriesDb.Count > 0)
            {
                var groupsDb = await db.QueryAsync<Model.QueryGroup>("SELECT * FROM QueryGroups");
                if (groupsDb != null && groupsDb.Count > 0)
                { 
                    var queries = await db.QueryAsync<Model.Query>(string.Format("SELECT * FROM Queries"));

                    var builder = new StringBuilder();
                    builder.Append("SELECT * FROM Entities WHERE ");

                    var i = 0;
                    foreach (var query in queries)
                    {
                        if (i > 0)
                        {
                            builder.Append(" OR ");
                        }
                        builder.Append(string.Format("(ModelId = '{0}' AND RepositoryId = '{1}')",
                            query.EntityModelId, query.RepositoryId));
                        i++;
                    }

                    var entities = await db.QueryAsync<Model.Entity>(builder.ToString());
                
                    foreach(var categoryDb in categoriesDb.OrderBy(c => c.Order))
                    {
                        var category = new Category(categoryDb.Id);
                        category.Name = categoryDb.Name;
                        category.Order = categoryDb.Order;

                        foreach(var group in groupsDb.Where(g => g.CategoryId == category.Id).OrderBy(g => g.Order))
                        {
                            var queryGroup = new QueryGroup(group.Id);
                            queryGroup.QueryType = group.QueryType;
                            queryGroup.Name = group.Name;
                            queryGroup.Image = group.Image;
                            queryGroup.Size = group.Size;
                            queryGroup.Order = group.Order;
                            //queryGroup.DisplayNameOnHomeScreen = group.DisplayNameOnHomeScreen;
                            foreach(var query in queries.Where(q => q.QueryGroupId == group.Id))
                            {
                                var entityDb = entities.First(e => e.ModelId == query.EntityModelId && e.RepositoryId == query.RepositoryId);
                                var repository = RepositoryBase.GetRepository(entityDb.RepositoryId);
                                var entity = Entity.New(entityDb.EntityType, entityDb.ModelId, entityDb.EntityName, repository, entityDb.Param1, entityDb.Param2);
                                queryGroup.Entities.Add(entity);
                            }
                            category.Groups.Add(queryGroup);
                        }

                        categories.Add(category);
                    }
                }
            }

            return categories;
        }

        public static async Task UpdateCategories(List<Category> categories)
        {
            var db = new SQLiteAsyncConnection(m_dbPath);

            var queries = new List<string>();

            // 1. Get all categories and manage
            var categoriesDb = await db.QueryAsync<Model.Category>("SELECT * FROM Categories");

            // 1.1 New or update categories
            int order = 0;
            foreach(var category in categories)
            {
                if(categoriesDb.Exists(c => c.Id == category.Id))
                {
                    queries.Add(string.Format("UPDATE Categories SET Name = '{0}', [Order] = {1} WHERE Id = '{2}';",
                        category.Name, order++, category.Id));
                }
                else
                {
                    queries.Add(string.Format(
                        "INSERT INTO Categories(Id, Name, [Order]) " +
                        "SELECT '{0}', '{1}', {2};",
                        category.Id, category.Name, order++));
                }
            }

            // 1.2 Old categories
            foreach(var categoryDb in categoriesDb)
            {
                if(!categories.Exists(c => c.Id == categoryDb.Id))
                {
                    queries.Add(string.Format("DELETE FROM Categories WHERE Id = '{0}'", categoryDb.Id));
                }
            }

            // 2. Get all groups and manage
            var groupsDb = await db.QueryAsync<Model.QueryGroup>("SELECT * FROM QueryGroups");

            foreach(var category in categories)
            {
                order = 0;
                foreach(var group in category.Groups)
                {
                    if(groupsDb.Exists(g => g.Id == group.Id))
                    {
                        queries.Add(string.Format(
                            "UPDATE QueryGroups SET " + 
                            "Size = {0}, [Order] = {1}, CategoryId = '{2}' WHERE Id = '{3}';",
                            (byte)group.Size, order++, category.Id, group.Id));
                    }
                    else throw new Exception("QueryGroup not found");
                }
            }

            foreach(var groupDb in groupsDb)
            {
                if(!categories.SelectMany(c => c.Groups).Any(g => g.Id == groupDb.Id))
                {
                    queries.Add(string.Format("DELETE FROM Queries WHERE QueryGroupId = '{0}'", groupDb.Id));
                    queries.Add(string.Format("DELETE FROM QueryGroups WHERE Id = '{0}'", groupDb.Id));
                }
            }

            foreach(var query in queries)
            {
                var closure = query;
                await db.ExecuteAsync(closure);
            }
        }

        public static async Task InsertOrUpdateQueryGroupAsync(QueryGroup group)
        {
            var db = new SQLiteAsyncConnection(m_dbPath);

            // 1. Entities
            var builder = new StringBuilder();
            builder.Append("SELECT * FROM Entities WHERE ");

            var i = 0;
            foreach (var entity in group.Entities)
            {
                if (i > 0)
                {
                    builder.Append(" OR ");
                }
                builder.Append(string.Format("(ModelId = '{0}' AND RepositoryId = '{1}')",
                    entity.Id, entity.Repository.Id));
                i++;
            }

            var requestEntities = await db.QueryAsync<Model.Entity>(builder.ToString());

            var entities = new List<Model.Entity>();
            foreach (var entity in group.Entities)
            {
                if (!requestEntities.Exists(r => r.ModelId == entity.Id && r.RepositoryId == entity.Repository.Id))
                {
                    entities.Add(Model.Entity.New(entity));
                }
            }
            if(entities.Count > 0) await db.InsertAllAsync(entities);


            // 2. QueryGroup
            Model.QueryGroup groupDb;
            var queryGroup = await db.QueryAsync<Model.QueryGroup>(string.Format("SELECT * FROM QueryGroups WHERE Id = '{0}'",
                group.Id));
            if (queryGroup.Count == 0)
            {
                // Create new category and set first
                var category = new Model.Category();
                category.Id = Guid.NewGuid();
                category.Order = 0;
                await db.ExecuteAsync("UPDATE Categories SET [Order] = [Order] + 1");
                await db.InsertAsync(category);

                groupDb = new Model.QueryGroup();
                groupDb.CategoryId = category.Id;
                groupDb.Id = group.Id;
                groupDb.Name = group.Name;
                groupDb.QueryType = group.QueryType;
                groupDb.Image = group.Image;
                groupDb.Size = QueryGroupSize.Medium;
                groupDb.Order = 0;
                //groupDb.DisplayNameOnHomeScreen = group.DisplayNameOnHomeScreen;
                await db.InsertAsync(groupDb);
            }
            else
            {
                groupDb = queryGroup[0];
                groupDb.Name = group.Name;
                groupDb.QueryType = group.QueryType;
                groupDb.Image = group.Image;
                //groupDb.DisplayNameOnHomeScreen = group.DisplayNameOnHomeScreen;
                await db.UpdateAsync(groupDb);
            }

            // 3. Queries
            await db.ExecuteAsync(string.Format("DELETE FROM Queries WHERE QueryGroupId = '{0}'", groupDb.Id));
            var queries = new List<Model.Query>();
            foreach (var entity in group.Entities)
            {
                var query = new Model.Query();
                query.EntityModelId = entity.Id;
                query.RepositoryId = entity.Repository.Id;
                query.QueryGroupId = groupDb.Id;
                queries.Add(query);
            }
            await db.InsertAllAsync(queries);
        }
    }
}
